﻿CREATE PROCEDURE [dbo].[proc_Statistics_Financial_Tongji_Rbb_G_Sort]
	(
		@CompanyId VARCHAR(500),
		@StartDate nchar(10),
		@EndDate nchar(10),
		@sort VARCHAR(3000),
		@StartIndex int,
		@EndIndex int
	)
AS
DECLARE @strSql VARCHAR(max),@strSql1 VARCHAR(max),@strSql2 VARCHAR(max),@strSql3 VARCHAR(max)
--(Select M.CompanyNumber From ManagementArea M Where M.Id=Mid) ASC,dwbh
	SET @strSql='	
	DECLARE @strDate DATETIME	
	--定义时间临时表
	declare @TempSca table(d_str DATETIME )
	set @strDate=CONVERT(DATETIME, '''+@StartDate+''')
	WHILE @strDate<=CONVERT(DATETIME,'''+@EndDate+''')
	BEGIN
		INSERT INTO @TempSca(d_str) VALUEs(@strDate)
		set @strDate=DATEADD(d,1, @strDate)
	END	
	
	;WITH list As(Select 
	  *	
	   from (select [CompanyId],[CompanyName]
      ,isnull([OAA],0) OAA      ,isnull([OAB],0) OAB      ,isnull([OBA],0) OBA      ,isnull([OBB],0) OBB
      ,isnull([OBC],0) OBC      ,isnull([OBD],0) OBD      ,isnull([OBE],0) OBE      ,isnull([OBF],0) OBF
      ,isnull([OBG],0) OBG      ,isnull([OBH],0) OBH      ,isnull([OCA],0) OCA      ,isnull([OCB],0) OCB
      ,isnull([ODA],0) ODA      ,isnull([ODB],0) ODB      ,isnull([IAA],0) IAA      ,isnull([IAB],0) IAB
      ,ISNULL((Select Sum(YingShouPrice-ShiShouPrice) From  [Order] Where [Order].CompanyId=Statistics_Financial.CompanyId 
      and Convert(nchar(10),AuditDate,120)=Convert(nchar(10),Statistics_Financial.CreateDate,120)   AND StateId>=1   ),0)+
			ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge WHERE CompanyId=Statistics_Financial.CompanyId  and Convert(nchar(10),UpTime,120)=Convert(nchar(10),Statistics_Financial.CreateDate,120) ),0)  As IAC--AND (NOE_Flag<>2)| and oid IN (SELECT oid FROM [Order] o WHERE companyId=Statistics_Financial.CompanyId AND o.NOE_Flag<>2 )
      ,ISNULL((Select Sum(CostPrice) From Project Where Project.CompanyId=Statistics_Financial.CompanyId and Convert(nchar(10),CreateDate,120) =Convert(nchar(10),Statistics_Financial.CreateDate,120) and FinanceAudit=1 ),0)+
			ISNULL((SELECT sum(dch_yf) FROM Order_Change_Hedge WHERE CompanyId=Statistics_Financial.CompanyId and Convert(nchar(10),UpTime,120)=Convert(nchar(10),Statistics_Financial.CreateDate,120) ),0) As IAD
      ,isnull([IAE],0) IAE      ,isnull([IAF],0) IAF
      ,isnull([IAG],0) IAG      ,isnull([IBA],0) IBA      ,isnull([IBB],0) IBB      ,isnull([IBC],0) IBC
      ,isnull([IBD],0) IBD      ,isnull([IBDA],0) IBDA      ,isnull([IBDB],0) IBDB      ,isnull([IBE],0) IBE
      ,isnull([IBEA],0) IBEA      ,isnull([IBEB],0) IBEB      ,isnull([IBF],0) IBF      ,isnull([IBG],0) IBG
      ,isnull([IBH],0) IBH      ,isnull([IBHA],0) IBHA      ,isnull([IBHB],0) IBHB      ,isnull([IBI],0) IBI
      ,isnull([IBIA],0) IBIA      ,isnull([IBIB],0) IBIB      ,isnull([IBJ],0) IBJ      ,isnull([IBK],0) IBK
      ,isnull([IBL],0) IBL
      ,[CreateDate] from Statistics_Financial where CompanyId='+@CompanyId+' and Convert(nchar(10),CreateDate,120) between '''+@StartDate+''' and '''+@EndDate+''' ) as a)'
	   	
	SET @strSql2=' select *,(select count(1) as DataCount from @TempSca) as DataCount from (
	select *,ROW_NUMBER() OVER (ORDER BY '+@sort+')AS Row from ( Select * From list  UNION 
	SELECT 0,'''',0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
	,0,0,0,0,0,0,0,0,0,0,0,0,0,d_str FROM @TempSca WHERE CONVERT(VARCHAR(10),d_str,120) NOT IN (SELECT CONVERT(VARCHAR(10),CreateDate,120) FROM list )
	
	) as a) as ab,(SELECT isnull(SUM(IAA),0) AS IAA_a,
	isnull(SUM(IAB),0) AS IAB_a,
	isnull(SUM(IAC),0) AS IAC_a,
	isnull(SUM(IAD),0) AS IAD_a,isnull(SUM(IAE),0) AS IAE_a,
	isnull(SUM(IAF),0) AS IAF_a,isnull(SUM(IAG),0) AS IAG_a 
	
	,isnull(SUM(OAA),0) AS OAA_a,isnull(SUM(OAB),0) AS OAB_a,isnull(SUM(OBA),0) AS OBA_a,isnull(SUM(OBB),0) AS OBB_a
	,isnull(SUM(OBC),0) AS OBC_a,isnull(SUM(OBD),0) AS OBD_a,isnull(SUM(OBE),0) AS OBE_a,isnull(SUM(OBF),0) AS OBF_a
	,isnull(SUM(OBG),0) AS OBG_a,isnull(SUM(OBH),0) AS OBH_a,isnull(SUM(OCA),0) AS OCA_a,isnull(SUM(OCB),0) AS OCB_a
	,isnull(SUM(ODA),0) AS ODA_a,isnull(SUM(ODB),0) AS ODB_a,
	
	isnull(SUM(IBA),0) AS IBA_a,isnull(SUM(IBB),0) AS IBB_a,isnull(SUM(IBD),0) AS IBD_a,isnull(SUM(IBE),0) AS IBE_a
	,isnull(SUM(IBC),0) AS IBC_a,isnull(SUM(IBF),0) AS IBF_a,isnull(SUM(IBG),0) AS IBG_a,isnull(SUM(IBH),0) AS IBH_a
	,isnull(SUM(IBI) ,0)AS IBI_a,isnull(SUM(IBJ),0) AS IBJ_a,isnull(SUM(IBK),0) AS IBK_a,isnull(SUM(IBL),0) AS IBL_a
	 FROM list) AS n Where Row Between '+Convert(varchar,@StartIndex)+' and '+Convert(varchar,@EndIndex)+' 
	 
	
	 '
	 SET @strSql3=' ORDER BY '+@sort+''
	 --PRINT (@strSql+@strSql1+@strSql2)  +@strSql1+@strSql2+@strSql3
EXEC(@strSql+@strSql2+@strSql3)
PRINT(@strSql+@strSql2+@strSql3)

	RETURN
